package org.zhangyp.pdf;

import org.dhatim.fastexcel.reader.ReadableWorkbook;
import org.dhatim.fastexcel.reader.Row;
import org.dhatim.fastexcel.reader.Sheet;

import java.io.*;
import java.util.*;

/**
 * 使用FastExcel库读取Excel文件的工具类
 * FastExcel是一个轻量级、高性能的Excel处理库
 */
public class FastExcelReader {

    /**
     * 读取Excel文件的所有工作表数据
     *
     * @param filePath Excel文件路径
     * @return 包含所有工作表数据的Map，key为工作表名，value为该工作表的数据行列表
     * @throws IOException 当读取文件发生错误时抛出
     */
    public static Map<String, List<Map<String, Object>>> readExcel(String filePath) throws IOException {
        Map<String, List<Map<String, Object>>> result = new HashMap<>();

        try (InputStream is = new FileInputStream(new File(filePath));
             ReadableWorkbook wb = new ReadableWorkbook(is)) {

            // 遍历所有工作表
            List<Sheet> sheets = wb.getSheets().toList();
            for (Sheet sheet : sheets) {
                String sheetName = sheet.getName();
                List<Map<String, Object>> sheetData = readSheet(sheet);
                result.put(sheetName, sheetData);
            }
        }

        return result;
    }

    /**
     * 读取指定工作表的数据
     *
     * @param sheet 工作表对象
     * @return 工作表的数据行列表，每行数据以Map形式存储，key为列名，value为单元格值
     * @throws IOException 当读取工作表发生错误时抛出
     */
    private static List<Map<String, Object>> readSheet(Sheet sheet) throws IOException {
        List<Map<String, Object>> rowsData = new ArrayList<>();
        List<String> headers = null;

        // 遍历工作表中的所有行
        try {
            for (Row row : sheet.read()) {
                if (headers == null) {
                    // 第一行作为表头
                    headers = new ArrayList<>();
                    for (int i = 0; i < row.getCellCount(); i++) {
                        headers.add(row.getCellText(i));
                    }
                } else {
                    // 数据行
                    Map<String, Object> rowData = new HashMap<>();
                    for (int i = 0; i < Math.min(row.getCellCount(), headers.size()); i++) {
                        if (i < headers.size()) {
                            String header = headers.get(i);
                            Object cellValue = getCellValue(row, i);
                            rowData.put(header, cellValue);
                        }
                    }
                    rowsData.add(rowData);
                }
            }
        } catch (Exception e) {
            throw new IOException("Error reading sheet: " + sheet.getName(), e);
        }

        return rowsData;
    }

    /**
     * 获取单元格的值，根据单元格类型返回对应的数据类型
     *
     * @param row       行对象
     * @param cellIndex 单元格索引
     * @return 单元格的值
     */
    private static Object getCellValue(Row row, int cellIndex) {
        if (cellIndex >= row.getCellCount()) {
            return null;
        }

        try {
            // 尝试读取为不同类型的值
            // FastExcel的getCellValue返回的是Optional对象
            Optional<String> cellValue = row.getCellRawValue(cellIndex);
            if (cellValue.isPresent()) {
                return cellValue.get();
            } else {
                // 如果值不存在，返回单元格文本
                return row.getCellText(cellIndex);
            }
        } catch (Exception e) {
            // 发生异常时返回单元格文本
            return row.getCellText(cellIndex);
        }
    }

    /**
     * 读取指定索引的工作表（从0开始）
     *
     * @param filePath   Excel文件路径
     * @param sheetIndex 工作表索引
     * @return 工作表的数据行列表
     * @throws IOException 当读取文件或工作表发生错误时抛出
     */
    public static List<Map<String, Object>> readSheetByIndex(String filePath, int sheetIndex) throws IOException {
        try (InputStream is = new FileInputStream(new File(filePath));
             ReadableWorkbook wb = new ReadableWorkbook(is)) {

            Optional<Sheet> sheetOptional = wb.getSheet(sheetIndex);
            if (sheetOptional.isPresent()) {
                return readSheet(sheetOptional.get());
            } else {
                throw new IOException("Sheet not found: " + sheetIndex);
            }
        }
    }

    /**
     * 主方法，用于测试FastExcel读取Excel文件的功能
     *
     * @param args 命令行参数
     */
    public static void main(String[] args) {
        try {
            // 替换为您的Excel文件路径
            String filePath = "C:\\Users\\10160\\my\\drg\\资源费用明细.xlsx";
            String tableName = "medical_fee_details_temp";
            // 输出SQL文件路径
            String sqlOutputPath = "C:\\Users\\10160\\my\\drg\\generated_" + tableName + ".sql";

            List<Map<String, Object>> dataList = readSheetByIndex(filePath, 0);

            // 使用try-with-resources确保文件正确关闭
            try (FileWriter writer = new FileWriter(sqlOutputPath)) {
                List<String> keys = new ArrayList<>();
                List<List<String>> valuesList = new ArrayList<>();
                for (Map<String, Object> data : dataList) {
                    keys.clear();
                    List<String> values = new ArrayList<>();
                    for (Map.Entry<String, Object> entry : data.entrySet()) {
                        keys.add(entry.getKey());
                        String value = entry.getValue().toString();
                        if (value.contains("'")){
                            value = value.replace("'", "\\'");
                        }
                        if(value.isEmpty()) {
                            values.add("NULL");
                        } else {
                            values.add("'" + value + "'");
                        }
                    }
                    valuesList.add(values);
                    if(valuesList.size() == 1000){
                        List<String> valuesSql = new ArrayList<>();
                        for(List<String> row : valuesList){
                            valuesSql.add("(" + String.join(",", row) + ")");
                        }
                        String sql = "INSERT INTO " + tableName + "(" + String.join(",", keys) + ") VALUES " + String.join(",", valuesSql) + ";";
                        // 写入文件
                        writer.write(sql + System.lineSeparator());
                        valuesList.clear();
                    }
                }
                if(valuesList.size() > 0){
                    List<String> valuesSql = new ArrayList<>();
                    for(List<String> row : valuesList){
                        valuesSql.add("(" + String.join(",", row) + ")");
                    }
                    String sql = "INSERT INTO " + tableName + "(" + String.join(",", keys) + ") VALUES " + String.join(",", valuesSql) + ";";
                    // 写入文件
                    writer.write(sql + System.lineSeparator());
                }
                System.out.println("SQL已成功写入文件：" + sqlOutputPath);
            }
        } catch (IOException e) {
            System.err.println("读取Excel文件或写入SQL文件时发生错误：" + e.getMessage());
            e.printStackTrace();
        }
    }
    public static void zd_logical_relation() {
        try {
            // 替换为您的Excel文件路径
            String filePath = "C:\\Users\\10160\\my\\drg\\诊断逻辑判断关系范围.xlsx";
            String tableName = "zd_logical_relation";
            // 输出SQL文件路径
            String sqlOutputPath = "C:\\Users\\10160\\my\\drg\\generated_" + tableName + ".sql";

            List<Map<String, Object>> dataList = readSheetByIndex(filePath, 1);

            // 使用try-with-resources确保文件正确关闭
            try (FileWriter writer = new FileWriter(sqlOutputPath)) {
                List<String> keys = new ArrayList<>();
                List<List<String>> valuesList = new ArrayList<>();
                for (Map<String, Object> data : dataList) {
                    keys.clear();
                    String zdCodeStr = data.get("编码").toString();
                    zdCodeStr = zdCodeStr.replaceAll("，", ",");
                    String[] split = zdCodeStr.split(",");
                    List<Map<String, Object>> zd_code_list = new ArrayList<>();
                    for (String zdCode : split) {
                        String zd_code_start;
                        String zd_code_end;
                        String[] split1 = zdCode.split("-");
                        if (split1.length == 2) {
                            zd_code_start = split1[0];
                            zd_code_end = split1[1];
                        } else {
                            zd_code_start = zdCode;
                            zd_code_end = zdCode;
                        }
                        int first_match_len = zd_code_start.length();
                        Map<String, Object> zd_code = new HashMap<>();
                        zd_code.put("zd_code_start", zd_code_start);
                        zd_code.put("zd_code_end", zd_code_end);
                        zd_code.put("first_match_len", first_match_len);
                        zd_code_list.add(zd_code);
                    }

                    String zd_name = data.get("章、节名称").toString();

                    Object xiyaoCodeObj = data.get("西药编号");
                    if (xiyaoCodeObj != null) {
                        String xiyaoCodeStr = xiyaoCodeObj.toString();
                        addValues(xiyaoCodeStr, zd_code_list, zd_name, valuesList);
                    }

                    Object zhongchengyaoCodeObj = data.get("中成药编号");
                    if (zhongchengyaoCodeObj != null) {
                        String zhongchengyaoCodeStr = zhongchengyaoCodeObj.toString();
                        addValues(zhongchengyaoCodeStr, zd_code_list, zd_name, valuesList);
                    }


                    if (valuesList.size() == 1000) {
                        List<String> valuesSql = new ArrayList<>();
                        for (List<String> row : valuesList) {
                            valuesSql.add("(" + String.join(",", row) + ")");
                        }
                        String sql = "INSERT INTO " + tableName + "(first_match_len, zd_code_start, zd_code_end, zd_name, resource_type, resource_sn_start, resource_sn_end) VALUES " + String.join(",", valuesSql) + ";";
                        // 写入文件
                        writer.write(sql + System.lineSeparator());
                        valuesList.clear();
                    }
                }
                if (valuesList.size() > 0) {
                    List<String> valuesSql = new ArrayList<>();
                    for (List<String> row : valuesList) {
                        valuesSql.add("(" + String.join(",", row) + ")");
                    }
                    String sql = "INSERT INTO " + tableName + "(first_match_len, zd_code_start, zd_code_end, zd_name, resource_type, resource_sn_start, resource_sn_end) VALUES " + String.join(",", valuesSql) + ";";
                    // 写入文件
                    writer.write(sql + System.lineSeparator());
                }
                System.out.println("SQL已成功写入文件：" + sqlOutputPath);
            }
        } catch (IOException e) {
            System.err.println("读取Excel文件或写入SQL文件时发生错误：" + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void addValues(String resourceCodeStr, List<Map<String, Object>> zd_code_list, String zd_name, List<List<String>> valuesList) {
        if (resourceCodeStr == null || resourceCodeStr.isEmpty()) {
            return;
        }
        resourceCodeStr = resourceCodeStr.trim();
        resourceCodeStr = resourceCodeStr.replaceAll("，", ",");
        String[] xiyaoSplit = resourceCodeStr.split(",");
        for (String resource : xiyaoSplit) {
            if (resource == null || resource.isEmpty()) {
                continue;
            }
            resource = resource.trim();
            String resource_start;
            String resource_end;
            String[] split1 = resource.split("-");
            if (split1.length == 2) {
                resource_start = split1[0];
                resource_end = split1[1];
            } else {
                resource_start = resource;
                resource_end = resource;
            }
            resource_start = resource_start.trim();
            resource_end = resource_end.trim();
            String resource_type = resource_start.substring(0, 1).toUpperCase();
            int resource_sn_start = Integer.parseInt(resource_start.substring(1));
            int resource_sn_end = Integer.parseInt(resource_end.substring(1));
            for (Map<String, Object> zd_code : zd_code_list) {
                String zd_code_start = (String) zd_code.get("zd_code_start");
                String zd_code_end = (String) zd_code.get("zd_code_end");
                int first_match_len = (int) zd_code.get("first_match_len");
                List<String> values = new ArrayList<>();
                values.add(first_match_len + "");
                values.add("'" + zd_code_start + "'");
                values.add("'" + zd_code_end + "'");
                values.add("'" + zd_name + "'");
                values.add("'" + resource_type + "'");
                values.add(resource_sn_start + "");
                values.add(resource_sn_end + "");
                valuesList.add(values);
            }
        }
    }


    public static void zd_resource_list(String excelName, String snKey, String nameKey) {
        try {
            // 替换为您的Excel文件路径
            String filePath = "C:\\Users\\10160\\my\\drg\\数据源\\数据源\\" + excelName + ".xlsx";
            String tableName = "zd_resource_list";
            // 输出SQL文件路径
            String sqlOutputPath = "C:\\Users\\10160\\my\\drg\\generated_" + tableName + "_" + excelName + ".sql";
            List<Map<String, Object>> dataList = readSheetByIndex(filePath, 0);
            // 使用try-with-resources确保文件正确关闭
            try (FileWriter writer = new FileWriter(sqlOutputPath)) {
                List<List<String>> valuesList = new ArrayList<>();
                for (Map<String, Object> data : dataList) {
                    String snStr = data.get(snKey).toString();
                    snStr = snStr.trim();
                    String resource_type = snStr.substring(0, 1).toUpperCase();
                    int resource_sn = Integer.parseInt(snStr.substring(1));
                    Object nameObj = data.get(nameKey);
                    if (nameObj != null) {
                        String nameStr = nameObj.toString();
                        // 按换行符分割字符串
                        String[] nameLines = nameStr.split("\\n");
                        for (String nameLine : nameLines) {
                            nameLine = nameLine.trim();
                            nameLine = nameLine.replace(" ", "");
                            if (nameLine.isEmpty()) {
                                continue;
                            }
                            addValues(resource_type, resource_sn, nameLine, valuesList);
                        }
                    } else {
                        addValues(resource_type, resource_sn, "", valuesList);
                    }
                    if (valuesList.size() == 1000) {
                        List<String> valuesSql = new ArrayList<>();
                        for (List<String> row : valuesList) {
                            valuesSql.add("(" + String.join(",", row) + ")");
                        }
                        String sql = "INSERT INTO " + tableName + "(resource_type, resource_sn, resource_name) VALUES " + String.join(",", valuesSql) + ";";
                        // 写入文件
                        writer.write(sql + System.lineSeparator());
                        valuesList.clear();
                    }
                }
                if (valuesList.size() > 0) {
                    List<String> valuesSql = new ArrayList<>();
                    for (List<String> row : valuesList) {
                        valuesSql.add("(" + String.join(",", row) + ")");
                    }
                    String sql = "INSERT INTO " + tableName + "(resource_type, resource_sn, resource_name) VALUES " + String.join(",", valuesSql) + ";";
                    // 写入文件
                    writer.write(sql + System.lineSeparator());
                }
                System.out.println("SQL已成功写入文件：" + sqlOutputPath);
            }
        } catch (IOException e) {
            System.err.println("读取Excel文件或写入SQL文件时发生错误：" + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void addValues(String resource_type, int resource_sn, String name, List<List<String>> valuesList) {
        List<String> values = new ArrayList<>();
        values.add("'" + resource_type + "'");
        values.add(resource_sn + "");
        values.add(name.isEmpty() ? "NULL" : "'" + name + "'");
        valuesList.add(values);
    }

    /*public static void main(String[] args) {
        *//*zd_resource_list("服务数据源", "编号", "项目名称");
        zd_resource_list("耗材数据源", "编号", "耗材名称");
        zd_resource_list("手术数据源", "编号", "手术操作名称");
        zd_resource_list("特殊药品参考表", "特殊药品编号", "药品名称");
        zd_resource_list("西医部分参考表", "编号", "药品名称");
        zd_resource_list("制剂数据源", "编号", "药品商品名");
        zd_resource_list("中成药参考表", "中成药编码序号", "药品名称");
        zd_resource_list("中医饮片参考表", "中医饮片编号", "饮片名称");*//*

        zd_resource_list("西医部分参考表", "编号", "药品名称");
        zd_resource_list("中成药参考表", "中成药编码序号", "药品名称");
    }*/
}